package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.LogFactory;
import org.json.JSONArray;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.util.DBUtil;
import fy.util.MyUtil;

public class CheckFinanceSupplierExistDao {

	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(CheckFinanceSupplierExistDao.class);

	private String[] checkArray;

	private String msg;

	private String corpID;

	public void setCorpID(String corpID) {
		this.corpID = corpID;
	}

	public String getMsg() {
		return msg;
	}

	public void setCheckArray(String[] checkArray) {
		this.checkArray = checkArray;
	}

	/**
	 * 供应商名称都不存在返回true
	 * 
	 * @return boolean
	 */
	public boolean checkName() {
		boolean notExists = false;
		if (checkArray != null) {
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;

			try {
				conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

				StringBuilder sb = new StringBuilder()
						.append("select v.cvenname, v.cvencode,v.cvendefine1 from fy_supplier_vendor v left join fy_supplier_vendor_invclass i on v.pk_vendor = i.pk_vendor where (v.dr is null or v.dr = 0) and ( v.cvenname = ? ");

				for (int i = 1, length = checkArray.length; i < length; i++) {
					sb.append(" or v.cvenname = ? ");
				}
				sb.append(") and (i.cvendorclass = '99' and i.pk_corp = ? and ( i.dr is null or i.dr = '0') and (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )) or (v.cvendefine1 is null or v.cvendefine1 <> 'financial') and substr(v.cvencode, 0, 1) <> 'N') group by v.cvenname, v.cvencode,v.cvendefine1");
				pst = conn.prepareStatement(sb.toString());

				for (int i = 0, length = checkArray.length; i < length; i++) {
					pst.setString(i + 1, checkArray[i]);
				}
				pst.setString(checkArray.length + 1, corpID);

				rs = pst.executeQuery();

				sb = null;
				while (rs.next()) {
					if (sb == null) {
						sb = new StringBuilder();
					}
					sb.append(rs.getString(1))
							.append("(")
							.append(rs.getString(2))
							.append(")")
							.append("financial".equals(rs.getString(3)) ? "已经存在"
									: "为由《供应商导入批准表》导入供应商，不可由此表单导入")
							.append("\n");

				}

				msg = sb == null ? "1" : sb.toString();
				notExists = sb == null;
			} catch (Exception e) { 
				e.printStackTrace();
				log.error("查询财务供应商名称是否存在发生异常", e);
			} finally {
				DBUtil.close(rs, pst, conn);
			}
		} else {
			msg = "供应商名称不能为空";
		}
		return notExists;
	}

	/**
	 * 供应商名称都不存在返回true
	 * 
	 * @return boolean
	 */
	public String queryCode() {
		if (checkArray != null) {
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;

			try {
				conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

				StringBuilder sb = new StringBuilder()
						.append("select v.cvenname, v.cvencode from fy_supplier_vendor v where (v.dr is null or v.dr = 0) and ( v.cvenname = ? ");

				for (int i = 1, length = checkArray.length; i < length; i++) {
					sb.append(" or v.cvenname = ? ");
				}
				sb.append(")");
				pst = conn.prepareStatement(sb.toString());

				for (int i = 0, length = checkArray.length; i < length; i++) {
					pst.setString(i + 1, checkArray[i]);
				}

				rs = pst.executeQuery();

				sb = null;
				JSONArray ja = new JSONArray();
				while (rs.next()) {
					JSONObject jo = new JSONObject();
					jo.put("name", rs.getString(1));
					jo.put("code", rs.getString(2));
					ja.put(jo);
				}
				msg = new StringBuilder().append("{success:true,msg:")
						.append(ja.toString()).append("}").toString();
			} catch (Exception e) {
				e.printStackTrace();
				log.error("查询财务供应商编码发生异常", e);
				msg = "{success:false,msg:'查询财务供应商名称编码发生异常'}";
			} finally {
				DBUtil.close(rs, pst, conn);
			}
		} else {
			msg = "{success:false,msg:'供应商名称不能为空'}";
		}
		return msg;
	}

	/**
	 * 供应商名称都不存在返回true
	 * 
	 * @return boolean
	 */
	public void checkCode() {
		if (checkArray != null) {
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;

			try {
				conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

				StringBuilder sb = new StringBuilder()
						.append("select v.cvencode,v.cvenname,1 from fy_supplier_vendor v where v.dr = 0 and  v.cvencode in ( ? ");

				for (int i = 1, length = checkArray.length; i < length; i++) {
					sb.append(",? ");
				}
				sb.append(")");
				sb.append(
						" union all select v.vendors_num, v.vendors_num,2 from apps.FYG_PO_UAP_VENDORS@")
						.append(FyConfig.getERP_DB_CONFIG().getDblink())
						.append(" v where v.vendors_num in (? ");
				for (int i = 1, length = checkArray.length; i < length; i++) {
					sb.append(" , ? ");
				}
				sb.append(") group by v.vendors_num, v.vendors_num");
				pst = conn.prepareStatement(sb.toString());

				for (int i = 0, length = checkArray.length; i < length; i++) {
					pst.setString(i + 1, checkArray[i]);
					pst.setString(length + i + 1, checkArray[i]);
				}

				rs = pst.executeQuery();

				sb = null;
				JSONObject jo = new JSONObject();
				boolean notExists = true;
				List<String> codeList = new ArrayList<String>();
				StringBuilder erpSb = null;
				while (rs.next()) {
					if (MyUtil.strIsNotNull(rs.getString(1))) {
						int state = rs.getInt(3);
						String code = rs.getString(1);
				/*	if (state == 1) {
							if (sb == null) {
								sb = new StringBuilder()
										.append("以下供应商编码已经存在UAP系统中:");
							}
							sb.append("\n").append(rs.getString(1)).append("【")
									.append(rs.getString(2)).append("】");

							notExists = false;
							codeList.add(code);
						} else {
							if (codeList.indexOf(code) == -1) {
								if (erpSb == null) {
									erpSb = new StringBuilder()
											.append("以下供应商编码已经存在ERP系统中:");
								}
								erpSb.append("\n").append(rs.getString(1))
										.append("【").append(rs.getString(2))
										.append("】");
							}
						}*/
					}
				}
				jo.put("success", notExists);

				jo.put("msg",
						sb == null ? erpSb : sb.append(erpSb == null ? ""
								: erpSb));
				if (sb != null) {
					log.warn(sb);
				}
				if (erpSb != null) {
					log.warn(erpSb);
				}
				erpSb = null;
				sb = null;
				codeList = null;
				msg = jo.toString();
				jo = null;
			} catch (Exception e) {
				e.printStackTrace();
				log.error("查询财务供应商编号是否存在发生异常", e);
				msg = "{success : false, msg : '查询财务供应商编号是否存在发生异常'}";
			} finally {
				DBUtil.close(rs, pst, conn);
			}
		} else {
			msg = "{success : false, msg : '财务供应商编号不能为空'}";
		}
	}
}
